﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
namespace SuperSurvey.DAO
{
    public class DatabaseInteraction
    {
        public static string strConnect = ConfigurationManager.ConnectionStrings["SurveyConnectionString"].ConnectionString;
        private static SqlConnection connection;
        private static SqlCommand command;
        public static string message;
        private SqlDataAdapter myAdpter;
        public static SqlConnection GetConnection()
        {
            try
            {
                connection = new SqlConnection(strConnect);
            }
            catch (Exception)
            {
                return null;
            }
            return connection;
        }

        public static bool InteractionData(string storedProcedure, SqlParameter[] sqlParameter)
        {
            int row = -1;
            try
            {
                using (connection = GetConnection())
                {
                    connection.Open();
                    using (command = new SqlCommand(storedProcedure, connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(sqlParameter);
                        row = command.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                message = e.Message;
                return false;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return row > 0;
        }
        public DataTable excuteSelectQuery(String _sql, SqlParameter[] parameter)
        {
            myAdpter = new SqlDataAdapter();
            SqlCommand myCommand = new SqlCommand();
            DataTable myDataTable = new DataTable();
            myDataTable = null;
            DataSet myDataset = new DataSet();
            try
            {
                myCommand.Connection = GetConnection();
                myCommand.CommandText = _sql;
                myCommand.Parameters.AddRange(parameter);
                myAdpter.SelectCommand = myCommand;
                myAdpter.Fill(myDataset);
                myDataTable = myDataset.Tables[0];

            }
            catch (SqlException e)
            {
                Console.Write("Error - Connection.executeSelectQuery - Query: " + _sql + " \nException: " + e.StackTrace.ToString());
                return null;
            }
            finally
            { }

            return myDataTable;
        }

        public static DataTable GetSelectTable(string sp, SqlParameter[] sqlParamater)
        {
            DataTable table = new DataTable();
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                using(connection = GetConnection()){
                    connection.Open();
                    using(command= new SqlCommand(sp,connection)){
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(sqlParamater);
                        adapter.SelectCommand = command;
                        adapter.Fill(ds);
                        table = ds.Tables[0];
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            finally
            {
                if (connection != null) {
                    connection.Close();
                }
            }
            return table;   
        }

        public static DataTable GetSelectTable(string sp)
        {
            DataTable table = new DataTable();
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                using (connection = GetConnection())
                {
                    connection.Open();
                    using (command = new SqlCommand(sp, connection))
                    {
                        command.CommandType = CommandType.Text;                        
                        adapter.SelectCommand = command;
                        adapter.Fill(ds);
                        table = ds.Tables[0];
                    }
                }
            }
            catch (SqlException ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                }
            }
            return table;
        }

    }
}